package model

import (
	"database/sql"
	"fmt"
	"log"
	"time"
)

type StockFavorite struct {
	ID        int       `json:"id"`
	StockCode string    `json:"stock_code"`
	StockName string    `json:"stock_name"` // 名字
	Date      string    `json:"date"`       // 日期字段
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

// GetStockListPage 获取指定页数和每页条目数的股票列表数据
func GetStockList(page, pageSize int) ([]StockFavorite, error) {
	// 计算偏移量
	offset := (page - 1) * pageSize

	// 执行 SQL 查询
	rows, err := db.Query("SELECT id, stock_code, stock_name, date, created_at, updated_at FROM stock_favorites ORDER BY id LIMIT ?, ?", offset, pageSize)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var stockList []StockFavorite

	// 遍历查询结果集
	for rows.Next() {
		var createdAt, updatedAt []uint8 // 数据库中的时间字段以 []uint8 类型表示

		var stock StockFavorite
		err := rows.Scan(&stock.ID, &stock.StockCode, &stock.StockName, &stock.Date, &createdAt, &updatedAt)
		if err != nil {
			return nil, err
		}

		// 将 []uint8 类型的数据库时间字段转换为 time.Time 类型
		stock.CreatedAt, err = time.Parse("2006-01-02 15:04:05", string(createdAt))
		if err != nil {
			return nil, fmt.Errorf("error parsing created_at: %v", err)
		}

		stock.UpdatedAt, err = time.Parse("2006-01-02 15:04:05", string(updatedAt))
		if err != nil {
			return nil, fmt.Errorf("error parsing updated_at: %v", err)
		}

		stockList = append(stockList, stock)
	}

	// 检查 rows.Next() 中的错误
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return stockList, nil
}

// Save method to insert or update a stock record in the database
func (s *StockFavorite) StockFavorite() error {
	// Check if the stock with the same stock_code exists in the database
	var count int
	err := db.QueryRow("SELECT COUNT(*) FROM stock_favorites WHERE stock_code = ?", s.StockCode).Scan(&count)
	if err != nil {
		log.Printf("Failed to check if stock exists: %v", err)
		return err
	}

	s.Date = time.Now().Format("2006-01-02") // 格式化为 "年-月-日"

	// Prepare query based on whether the stock exists
	var query string
	if count > 0 {
		query = `
			UPDATE stock_favorites
			SET updated_at = ?
			WHERE stock_code = ?
		`
	} else {
		query = `
			INSERT INTO stock_favorites (stock_code, stock_name, date, created_at, updated_at)
			VALUES (?, ?, ?, ?, ?)
		`
	}

	// Execute the query
	var res sql.Result
	if count > 0 {
		res, err = db.Exec(query, time.Now(), s.StockCode)
	} else {
		res, err = db.Exec(query, s.StockCode, s.StockName, s.Date, time.Now(), time.Now())
	}
	if err != nil {
		log.Printf("Error saving stock: %v", err)
		return err
	}

	// Check the number of rows affected to determine if it was an update or insert
	rowsAffected, err := res.RowsAffected()
	if err != nil {
		log.Printf("Error getting rows affected: %v", err)
		return err
	}
	if count > 0 && rowsAffected > 0 {
		log.Printf("Stock with stock_code %s updated successfully", s.StockCode)
	} else if rowsAffected > 0 {
		log.Printf("Stock with stock_code %s inserted successfully", s.StockCode)
	}

	return nil
}

// DeleteStockFavorite 根据股票代码删除记录
func (s *StockFavorite) DeleteStockFavorite() error {
	_, err := db.Exec("DELETE FROM stock_favorites WHERE stock_code = ?", s.StockCode)
	if err != nil {
		log.Printf("Error deleting stock with code %s: %v", s.StockCode, err)
		return err
	}
	log.Printf("Stock with stock_code %s deleted successfully", s.StockCode)
	return nil
}
